<?php

namespace app\modules\Product\controllers;

use Yii;
use app\modules\Product\models\Product;
use yii\web\Controller;
use yii\web\NotFoundHttpException;
use yii\filters\VerbFilter;
use yii\helpers\ArrayHelper;
use yii\data\ActiveDataProvider;
use app\modules\Cost\models\Cost;
use app\modules\Manage\models\User;

class DataController extends Controller
{

	//数据统计
    public function actionIndex()
    {
        return $this->render('index');
    }
    


    //获取数据
    public function actionData(){

        Yii::$app->response->format = \yii\web\Response::FORMAT_JSON;

    	$model = Product::find()->where(['not', ['begin_date' => null]])->all();

    	$data = [];
    	if($model){
    		foreach($model as $key=>$m){
    			$data[$key]['p_id'] = $m->id;
    			$data[$key]['title'] = '【团号】'.$m->code.'【团名称】'.$m->name.'【开团人】'.$m->userName;
    			$data[$key]['start'] = date("Y-m-d",strtotime($m->begin_date));
    			$data[$key]['end'] = date("Y-m-d",strtotime("+1 day",strtotime($m->end_date)));
    			$data[$key]['backgroundColor'] = '#0073b7';
    		}
    	}

    	return $data;

    }

    //获取单个团队详情
    public function actionDetail($product_id){

    	$this->layout = '//empty';

        $product = Product::findOne($product_id);

    	return $this->render('detail',['product'=>$product]);

    }


    //获取改天的团队列表详情
    public function actionList($day=''){

        $this->layout = '//empty';
        $model = [];
        if($day){
            $model = Product::find()->where(['<=','begin_date',$day])->andWhere(['>=','end_date',$day])->all();
        }
        return $this->render('list',['model'=>$model,'day'=>$day]);

    }

    //团组收入统计
    public function actionStatis(){

        $product = Product::find()->where(['not', ['begin_date' => null]])->groupBy('user_id')->all();
        $user_arr_id = $user_arr = [];
        if($product){
            foreach($product as $p){
                $user_arr_id[] = $p->user_id;
                //$user_arr[$p->user_id] = $p->userName;
            }
        }

        if(Yii::$app->user->can('all_product')){

            $user_id = $user_arr_id;

        }

        if(!Yii::$app->user->can('all_product')){

            if(\yii::$app->user->identity->manage_user_id){
                $user_id = json_decode(\yii::$app->user->identity->manage_user_id);
                array_push($user_id,\yii::$app->user->id);
            }else{
                $user_id = [\yii::$app->user->id];
            }

        }

        if($user_id && is_array($user_id)){
            foreach($user_id as $val){
                $user_model = User::findOne($val);
                $user_arr[$val] = $user_model->name;
            }
        }
       
        $query = Product::find()->where(['not', ['begin_date' => null]]);

        $params = Yii::$app->request->get();

        if(isset($params['u_id']) && $params['u_id']){
            $query->andWhere(['user_id'=>$params['u_id']]);
        }else{
            $query->Where(['user_id'=>$user_id]);
        } 

        if(isset($params['from_date']) && isset($params['to_date']) && $params['from_date'] && $params['to_date']){
            $query->andWhere(['>=','begin_date',$params['from_date']]);
            $query->andWhere(['<=','begin_date',$params['to_date']]);
        }else{
            //获取当年团队数量
            $year = date("Y",time());
            $params['from_date'] = $year.'-01-01';
            $params['to_date'] = $year.'-12-31';
            $query->andWhere(['>=','begin_date',$params['from_date']]);
            $query->andWhere(['<=','begin_date',$params['to_date']]);

        }

        $query->andWhere(['status'=>[1,2,3,10]]);

        $data = ['num'=>0,'price'=>0,'profit_price'=>0,'not_income_price'=>0,'profit'=>0];

        $num = $query->count();
        $data['num'] = $num ? $num : 0;
        $model = $query->all();

        if($model){
            foreach($model as $one){
                $data['price'] += $one->price;
                $data['profit_price'] += $one->profitPrice;
                $data['not_income_price'] += $one->price - $one->incomePrice;
            }
        }

        if($data['price']){

            $data['profit'] = round(($data['profit_price'] / $data['price'])*100,2);
        }
        


        return $this->render('statis',['data'=>$data,'user_arr'=>$user_arr,'params'=>$params]);
    }

    //下载excel
    public function actionDownload(){

        $user_all = Product::find()->where(['not', ['begin_date' => null]])->groupBy('user_id')->all();
        $user_arr_id = [];
        if($user_all){
            foreach($user_all as $p){
                $user_arr_id[] = $p->user_id;
            }
        }

        if(Yii::$app->user->can('all_product')){

            $user_id = $user_arr_id;

        }

        if(!Yii::$app->user->can('all_product')){

            if(\yii::$app->user->identity->manage_user_id){
                $user_id = json_decode(\yii::$app->user->identity->manage_user_id);
                array_push($user_id,\yii::$app->user->id);
            }else{
                $user_id = [\yii::$app->user->id];
            }

        }

        $query = Product::find()->where(['not', ['begin_date' => null]]);

        $params = Yii::$app->request->get();

        if(isset($params['u_id']) && $params['u_id']){
            $query->andWhere(['user_id'=>$params['u_id']]);
        }else{
            $query->Where(['user_id'=>$user_id]);
        } 

        if(isset($params['from_date']) && isset($params['to_date']) && $params['from_date'] && $params['to_date']){
            $query->andWhere(['>=','begin_date',$params['from_date']]);
            $query->andWhere(['<=','begin_date',$params['to_date']]);
        }else{
            //获取当年团队数量
            $year = date("Y",time());
            $params['from_date'] = $year.'-01-01';
            $params['to_date'] = $year.'-12-31';
            $query->andWhere(['>=','begin_date',$params['from_date']]);
            $query->andWhere(['<=','begin_date',$params['to_date']]);

        }

        $query->andWhere(['status'=>[1,2,3,10]]);

        $products = $query->all();

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团组详情');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('微软雅黑');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $sheet->getColumnDimension('A')->setWidth(8);
        $sheet->getColumnDimension('B')->setWidth(10);
        $sheet->getColumnDimension('C')->setWidth(12);
        $sheet->getColumnDimension('D')->setWidth(12);
        $sheet->getColumnDimension('E')->setWidth(12);
        $sheet->getColumnDimension('F')->setWidth(25);
        $sheet->getColumnDimension('G')->setWidth(25);
        $sheet->getColumnDimension('H')->setWidth(25);
        $sheet->getColumnDimension('J')->setWidth(16);
        $sheet->getColumnDimension('K')->setWidth(16);
        $sheet->getColumnDimension('L')->setWidth(25);
        $sheet->getColumnDimension('Q')->setWidth(25);
        $sheet->getColumnDimension('R')->setWidth(25);

        $array = ['序号','年份','月份','团组性质','开团人','操作','客户名称','团组名称','人数','出发日期','返回日期','目的地','收入','毛利','毛利率','欠款金额','酒店集团','涉及金额'];

        $sheet->fromArray($array, NULL, 'A1');
        $sheet->getStyle("A1:R1")->getFont()->setSize(10)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');

        $key = 1;
        if($products){    
             foreach($products as $model){
                $sheet->setCellValue(sprintf("A%d",$key+1),$key);
                $sheet->setCellValue(sprintf("B%d",$key+1),date("Y",strtotime($model->begin_date)));
                $sheet->setCellValue(sprintf("C%d",$key+1),date("m",strtotime($model->begin_date)));
                $sheet->setCellValue(sprintf("D%d",$key+1),\yii::$app->params['product_type'][$model->product_type]);

                $sheet->setCellValue(sprintf("E%d",$key+1),$model->userName);
                $sheet->setCellValue(sprintf("F%d",$key+1),$model->operName);

                $sheet->setCellValue(sprintf("G%d",$key+1),$model->customer->name);
                $sheet->setCellValue(sprintf("H%d",$key+1),$model->name);
                $sheet->setCellValue(sprintf("I%d",$key+1),$model->num);
                $sheet->setCellValue(sprintf("J%d",$key+1),$model->begin_date);
                $sheet->setCellValue(sprintf("K%d",$key+1),$model->end_date);
                $sheet->setCellValue(sprintf("L%d",$key+1),$model->cityInfo);
                $sheet->setCellValue(sprintf("M%d",$key+1),$model->price);
                $sheet->setCellValue(sprintf("N%d",$key+1),$model->profitPrice);
                $sheet->setCellValue(sprintf("O%d",$key+1),$model->profit);
                $sheet->setCellValue(sprintf("P%d",$key+1),$model->price - $model->incomePrice);
                $brand = '';
                $brand_price = '';
                if($model->hotelGroupName){
                    foreach($model->hotelGroupName as $one){
                        $brand .= $one['name'].'/';
                        $brand_price .= array_sum($one['price']).'/';
                    }
                }
                $sheet->setCellValue(sprintf("Q%d",$key+1),$brand);
                $sheet->setCellValue(sprintf("R%d",$key+1),$brand_price);
                $key++;
             }

            $sheet->setCellValue(sprintf("A%d",$key+1),'合计');
            $sheet->setCellValue(sprintf("M%d",$key+1),"=SUM(M2:".sprintf("M%d",$key).')');
            $sheet->setCellValue(sprintf("N%d",$key+1),"=SUM(N2:".sprintf("N%d",$key).')');
            $sheet->setCellValue(sprintf("P%d",$key+1),"=SUM(P2:".sprintf("P%d",$key).')');

        }

        $sheet->setAutoFilter('A1:R1'); //设置筛选

        $styleArray = array(  
            'borders' => array(  
                'allborders' => array(  
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框   
                    'color' => array('argb' => '000000'),  
                ),  
            ),  
        );
        $sheet->getStyle(sprintf("A1:R%d",$key+1))->applyFromArray($styleArray);

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/product/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $file_name = sprintf("%s.xlsx","团组详情");


        header("Content-type: application/octet-stream");    
        header("Accept-Ranges: bytes");    
        header("Accept-Length: " . filesize($file));    
        header("Content-Disposition: attachment; filename=" .$file_name);    
        @readfile($file);
        exit();
      

    }

    //超期应收款统计
    public function actionOverPrice(){


        $query = Product::find()->where(['not', ['begin_date' => null]]);

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);


        return $this->render('price',['dataProvider'=>$dataProvider]);
    }

    //信用卡支出统计
    public function actionCreditCard(){

        //信用卡 [1,5,6]
        $query = Cost::find()->where(['status'=>1,'payment'=>[1,5,6]]);

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);

        return $this->render('card',['dataProvider'=>$dataProvider]);
    }

    
}
